import { query } from '../config/db.js';
import logger from '../config/logger.js';

// 初始化数据库，创建必要的表
async function initDatabase() {
  try {
    logger.info('开始数据库初始化...');

    // 创建用户表 users
    await query(`
      CREATE TABLE IF NOT EXISTS users (
        id INT AUTO_INCREMENT PRIMARY KEY,
        username VARCHAR(50) UNIQUE NOT NULL,
        password VARCHAR(100) NOT NULL,
        email VARCHAR(100) UNIQUE NOT NULL,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    `);
    logger.info('用户表创建成功');

    // 创建权限表 permissions
    await query(`
      CREATE TABLE IF NOT EXISTS permissions (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) UNIQUE NOT NULL,
        description TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    `);
    logger.info('权限表创建成功');

    // 创建用户-权限关联表 user_permissions
    await query(`
      CREATE TABLE IF NOT EXISTS user_permissions (
        user_id INT,
        permission_id INT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        PRIMARY KEY (user_id, permission_id),
        FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE,
        FOREIGN KEY (permission_id) REFERENCES permissions(id) ON DELETE CASCADE
      );
    `);
    logger.info('用户-权限关联表创建成功');

    // 创建学生表 students
    await query(`
      CREATE TABLE IF NOT EXISTS students (
        id INT AUTO_INCREMENT PRIMARY KEY,
        name VARCHAR(50) NOT NULL,
        student_id VARCHAR(20) UNIQUE NOT NULL,
        class_name VARCHAR(50) NOT NULL,
        age INT,
        gender ENUM('男', '女'),
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    `);
    logger.info('学生表创建成功');

    // 创建新生分班表 new_student_classes
    await query(`
      CREATE TABLE IF NOT EXISTS new_student_classes (
        id INT AUTO_INCREMENT PRIMARY KEY,
        student_name VARCHAR(50) NOT NULL,
        gender ENUM('男', '女'),
        class_name VARCHAR(50) NOT NULL,
        head_teacher VARCHAR(50) NOT NULL,
        head_teacher_phone VARCHAR(20) NOT NULL,
        class_location VARCHAR(100) NOT NULL,
        remarks TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    `);
    logger.info('新生分班表创建成功');

    // 创建社团表 clubs
    await query(`
      CREATE TABLE IF NOT EXISTS clubs (
        id INT AUTO_INCREMENT PRIMARY KEY,
        club_name VARCHAR(50) NOT NULL,
        member_count INT NOT NULL,
        location VARCHAR(100) NOT NULL,
        remarks TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    `);
    logger.info('社团表创建成功');

    // 创建社团报名表 club_registrations
    await query(`
      CREATE TABLE IF NOT EXISTS club_registrations (
        id INT AUTO_INCREMENT PRIMARY KEY,
        class_name VARCHAR(50) NOT NULL,
        student_name VARCHAR(50) NOT NULL,
        club_name VARCHAR(50) NOT NULL,
        club_location VARCHAR(100) NOT NULL,
        remarks TEXT,
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    `);
    logger.info('社团报名表创建成功');

    // 创建社团秒杀配置表 seckill_club_config
    await query(`
      CREATE TABLE IF NOT EXISTS seckill_club_config (
        id INT AUTO_INCREMENT PRIMARY KEY,
        seckill_status ENUM('未开始', '进行中', '已结束') NOT NULL DEFAULT '未开始',
        created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
        updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
      );
    `);
    logger.info('社团秒杀配置表创建成功');

    // 初始化秒杀配置数据（如果表为空）
    const configResult = await query('SELECT * FROM seckill_club_config');
    if (configResult.rows.length === 0) {
      await query('INSERT INTO seckill_club_config (seckill_status) VALUES (?)', ['未开始']);
      logger.info('社团秒杀配置初始化成功');
    }

    logger.info('数据库初始化成功完成');
  } catch (error) {
    logger.error('数据库初始化失败:', error);
    throw error;
  }
}

// 执行初始化
initDatabase().catch(error => {
  logger.error('数据库初始化过程中出错:', error);
  process.exit(1);
});